package com.augurit.gzcgwsp.excelimportexport.service.impl;

import java.util.Date;
import java.util.List;

import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.util.Region;
import org.dozer.DozerBeanMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.augurit.gzcgwsp.excelimportexport.dao.YwRqQyjyxkRqqxsbylbDao;
import com.augurit.gzcgwsp.excelimportexport.entity.YwRqQyjyxkRqqxdylb;
import com.augurit.gzcgwsp.excelimportexport.entity.YwRqQyjyxkRqqxsbylb;
import com.augurit.gzcgwsp.excelimportexport.service.IYwRqQyjyxkRqqxsbylbService;
import com.augurit.gzcgwsp.rqqyjyxk.entity.YwRqQyjyxkFinalRqqxdylb;
import com.augurit.gzcgwsp.rqqyjyxk.entity.YwRqQyjyxkFinalRqqxsbylb;
import com.augurit.gzcgwsp.rqqyjyxk.service.IYwRqQyjyxkFinalRqqxsbylbService;

@Service
@Transactional
public class YwRqQyjyxkRqqxsbylbServiceImpl implements IYwRqQyjyxkRqqxsbylbService {

	@Autowired
	private YwRqQyjyxkRqqxsbylbDao ywRqQyjyxkRqqxsbylbDao;
	@Autowired
	private IYwRqQyjyxkFinalRqqxsbylbService rqqxsbService;
	/**
	 * 保存 燃气抢险设备一览表
	 * @param YwRqQyjyxkRqqxsbylb
	 */
	public void save(YwRqQyjyxkRqqxsbylb ywRqQyjyxkRqqxsbylb) {
		ywRqQyjyxkRqqxsbylbDao.save(ywRqQyjyxkRqqxsbylb);
	}
	
	/**
	 * 根据企业id获取 燃气抢险设备一览表
	 * @param qyid
	 * @return
	 */
	private List<YwRqQyjyxkRqqxsbylb> listByQyid(Long qyid){
	    String hql = "from YwRqQyjyxkRqqxsbylb t where t.qyid='" + qyid + "'";
        List<YwRqQyjyxkRqqxsbylb> entityList = ywRqQyjyxkRqqxsbylbDao.find(hql);
        return entityList;
	}
	
	/**
	 * 解析 燃气抢险设备一览表
	 * @param hssfsheet
	 * @param acceptNo
	 * @param qyid
	 * @return
	 */
	public YwRqQyjyxkRqqxsbylb parseRqqxsbylb(HSSFSheet hssfsheet,String acceptNo, Long qyid, String username) {
		String xh = null;//序号
		String mc = null;//名称
		String ggxh = null;//规格型号
		String sl = null;//数量
		
		String hql = "from YwRqQyjyxkRqqxsbylb t where t.qyid = "+qyid+"";
			List<YwRqQyjyxkRqqxsbylb> list=ywRqQyjyxkRqqxsbylbDao.find(hql);
			for(int i=0;i<list.size();i++)
			{
				ywRqQyjyxkRqqxsbylbDao.delete(list.get(i));
			}
		
		try {
			
			HSSFRow hssfrow = hssfsheet.getRow(0);// 第一行
			int flag = 0;
			for (int j = 7; j < hssfsheet.getPhysicalNumberOfRows(); j++) {
				hssfrow = hssfsheet.getRow(j);				
				String aa = null;
				if (hssfrow.getCell((short) 0) == null) {
					aa = "";
				} else if (hssfrow.getCell((short) 0).getCellType() == 0) {
					aa = new Double(hssfrow.getCell((short) 0).getNumericCellValue()).toString();
				} else {// 如果EXCEL表格中的数据类型为字符串型
					aa = hssfrow.getCell((short) 0).getStringCellValue().trim();
				}				
				if("燃气抢险设备一览表".equals(aa)){
//					System.out.println("jjjjjjjjjjjjj=========="+j);
					flag = j;
				}
				
			}
//			System.out.println("11111111");
			// 遍历该行所有的行,j表示行数 getPhysicalNumberOfRows行的总数
			for (int i = (flag+2); i < hssfsheet.getPhysicalNumberOfRows(); i++) {

				hssfrow = hssfsheet.getRow(i);
				// 判断是否还存在需要导入的数据
				if (hssfrow == null) {
					// System.out.println("这里已没有数据，在第" + i + "列,第" + j + "行");
					break;
				}			
				
					if (hssfrow.getCell((short) 0) == null) {
						xh = "";
					} else if (hssfrow.getCell((short) 0).getCellType() == 0) {
						xh = new Double(hssfrow.getCell((short) 0).getNumericCellValue()).toString();
					} else {// 如果EXCEL表格中的数据类型为字符串型
						xh = hssfrow.getCell((short) 0).getStringCellValue().trim();
					}
					if (hssfrow.getCell((short) 1) == null) {
						mc = "";
					} else if (hssfrow.getCell((short) 1).getCellType() == 0) {
						mc = new Double(hssfrow.getCell((short) 1).getNumericCellValue()).toString();
					} else {// 如果EXCEL表格中的数据类型为字符串型
						mc = hssfrow.getCell((short) 1).getStringCellValue().trim();
					}
					if (hssfrow.getCell((short) 3) == null) {
						ggxh = "";
					} else if (hssfrow.getCell((short) 3).getCellType() == 0) {
						ggxh = new Double(hssfrow.getCell((short) 3).getNumericCellValue()).toString();
					} else {// 如果EXCEL表格中的数据类型为字符串型
						ggxh = hssfrow.getCell((short) 3).getStringCellValue().trim();
					}
					if (hssfrow.getCell((short) 5) == null) {
						sl = "";
					} else if (hssfrow.getCell((short) 5).getCellType() == 0) {
						sl = new Double(hssfrow.getCell((short) 5).getNumericCellValue()).toString();
					} else {// 如果EXCEL表格中的数据类型为字符串型
						sl = hssfrow.getCell((short) 5).getStringCellValue().trim();
					}
					
					
					if(StringUtils.isEmpty(xh)){
						break;
					}else{
						YwRqQyjyxkRqqxsbylb ywRqQyjyxkRqqxsbylb = new YwRqQyjyxkRqqxsbylb();
						ywRqQyjyxkRqqxsbylb.setQyid(qyid);
						ywRqQyjyxkRqqxsbylb.setAcceptNo(acceptNo);
						ywRqQyjyxkRqqxsbylb.setUserName(username);
						ywRqQyjyxkRqqxsbylb.setUploadDate(new Date());
						ywRqQyjyxkRqqxsbylb.setXh(xh);
						ywRqQyjyxkRqqxsbylb.setMc(mc);
						ywRqQyjyxkRqqxsbylb.setGgxh(ggxh);
						ywRqQyjyxkRqqxsbylb.setSl(sl);
						this.save(ywRqQyjyxkRqqxsbylb);
					}
					
				}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	/**
     * 解析 燃气抢险设备一览表
     * @param hssfsheet
     * @param acceptNo
     * @param qyid
     * @return
     */
    public HSSFSheet expRqqxsbylb(HSSFSheet hssfsheet,String acceptNo, Long qyid, String username,HSSFCellStyle cs,String exptype){
       if("qyxx".equals(exptype)){
    	   List<YwRqQyjyxkFinalRqqxsbylb> entityList =    rqqxsbService.getInfosByQyid(qyid);;
           if(entityList == null || entityList.size() <= 0) return null;
           if(entityList!=null){
           try {
               /**
                * i代表生成的Excel中列表行，j表示将要产生多少行数据
                */
               for(int i = 13, j = 0; j< entityList.size(); i++){
                  // if(i < 7) continue;
            	   YwRqQyjyxkFinalRqqxsbylb entity = entityList.get(j);
                   if(entity != null){
                       HSSFRow row = hssfsheet.createRow(i);// 创建一个行对象
                       HSSFCell cell0 = row.createCell(0);// 在本行的第一个单元格中写入数据
                       HSSFRichTextString text0 = new HSSFRichTextString(entity.getXh()==null?"":entity.getXh());// 声明文本内容
                       cell0.setCellValue(text0);// 将文本对象加入单元格
                       cell0.setCellStyle(cs); 
                       
                      hssfsheet.addMergedRegion(new Region(1,(short)1,1,(short)2)); // 四个参数分别是：起始行，起始列，结束行，结束列
                       HSSFCell cell1 = row.createCell(1);// 在本行的第一个单元格中写入数据
                       HSSFCell cell2= row.createCell(2);// 
                       HSSFRichTextString text1 = new HSSFRichTextString(entity.getMc()==null?"":entity.getMc());// 声明文本内容
                       cell1.setCellValue(text1);// 将文本对象加入单元格
                       cell1.setCellStyle(cs); 
                       cell2.setCellStyle(cs); 
                      hssfsheet.addMergedRegion(new Region(1,(short)3,1,(short)4)); // 四个参数分别是：起始行，起始列，结束行，结束列
                       HSSFCell cell3 = row.createCell(3);// 在本行的第一个单元格中写入数据
                       HSSFCell cell4 = row.createCell(4);//主要是设置样式
                       HSSFRichTextString text3 = new HSSFRichTextString(entity.getGgxh()==null?"":entity.getGgxh());// 声明文本内容
                       cell3.setCellValue(text3);// 将文本对象加入单元格
                       cell4.setCellStyle(cs); 
                       cell3.setCellStyle(cs);
                       HSSFCell cell5 = row.createCell(5);// 在本行的第一个单元格中写入数据
                       HSSFRichTextString text5 = new HSSFRichTextString(entity.getSl()==null?"":entity.getSl());// 声明文本内容
                       cell5.setCellValue(text5);// 将文本对象加入单元格
                       cell5.setCellStyle(cs); 
                   }
                   j++;
               }
           } catch (Exception e) {
               e.printStackTrace();
           }
          }
       }else{
    	   List<YwRqQyjyxkRqqxsbylb> entityList = this.listByQyid(qyid);
           if(entityList == null || entityList.size() <= 0) return null;
           if(entityList!=null){
           try {
               /**
                * i代表生成的Excel中列表行，j表示将要产生多少行数据
                */
               for(int i = 13, j = 0; j< entityList.size(); i++){
                  // if(i < 7) continue;
                   
               	 YwRqQyjyxkRqqxsbylb entity = entityList.get(j);
                   if(entity != null){
                       HSSFRow row = hssfsheet.createRow(i);// 创建一个行对象
                       HSSFCell cell0 = row.createCell(0);// 在本行的第一个单元格中写入数据
                       HSSFRichTextString text0 = new HSSFRichTextString(entity.getXh()==null?"":entity.getXh());// 声明文本内容
                       cell0.setCellValue(text0);// 将文本对象加入单元格
                       cell0.setCellStyle(cs); 
                       
                      hssfsheet.addMergedRegion(new Region(1,(short)1,1,(short)2)); // 四个参数分别是：起始行，起始列，结束行，结束列
                       HSSFCell cell1 = row.createCell(1);// 在本行的第一个单元格中写入数据
                       HSSFCell cell2= row.createCell(2);// 
                       HSSFRichTextString text1 = new HSSFRichTextString(entity.getMc()==null?"":entity.getMc());// 声明文本内容
                       cell1.setCellValue(text1);// 将文本对象加入单元格
                       cell1.setCellStyle(cs); 
                       cell2.setCellStyle(cs); 
                      hssfsheet.addMergedRegion(new Region(1,(short)3,1,(short)4)); // 四个参数分别是：起始行，起始列，结束行，结束列
                       HSSFCell cell3 = row.createCell(3);// 在本行的第一个单元格中写入数据
                       HSSFCell cell4 = row.createCell(4);//主要是设置样式
                       HSSFRichTextString text3 = new HSSFRichTextString(entity.getGgxh()==null?"":entity.getGgxh());// 声明文本内容
                       cell3.setCellValue(text3);// 将文本对象加入单元格
                       cell4.setCellStyle(cs); 
                       cell3.setCellStyle(cs);
                       HSSFCell cell5 = row.createCell(5);// 在本行的第一个单元格中写入数据
                       HSSFRichTextString text5 = new HSSFRichTextString(entity.getSl()==null?"":entity.getSl());// 声明文本内容
                       cell5.setCellValue(text5);// 将文本对象加入单元格
                       cell5.setCellStyle(cs); 
                   }
                   j++;
               }
           } catch (Exception e) {
               e.printStackTrace();
           }
          }
        }
        return hssfsheet;
    }

    private void setRegionStyle(HSSFSheet sheet, Region region , HSSFCellStyle cs) {
        int toprowNum = region.getRowFrom();
        for (int i = region.getRowFrom(); i <= region.getRowTo(); i ++) {
            HSSFRow row = sheet.getRow(i);
            for (int j = region.getColumnFrom(); j <= region.getColumnTo(); j++) {
                HSSFCell cell = row.getCell((short)j);
                cell.setCellStyle(cs);
            }
        }
 }

    
    public void importFinalQyData(String acceptNo, Long qyid) {
        List<YwRqQyjyxkRqqxsbylb> list=this.getDatas(acceptNo, qyid);
        for(YwRqQyjyxkRqqxsbylb entity:list){
            YwRqQyjyxkFinalRqqxsbylb finalentity = new DozerBeanMapper().map(entity, YwRqQyjyxkFinalRqqxsbylb.class);
            finalentity.setId(null);
            this.rqqxsbService.save(finalentity);
        }
    }
    
    /**
     * 
     * @return
     */
    private List<YwRqQyjyxkRqqxsbylb> getDatas(String acceptNo, Long qyid){
        String hql = "from YwRqQyjyxkRqqxsbylb t where t.qyid='" + qyid + "' and t.acceptNo='"+acceptNo+"'";
        return this.ywRqQyjyxkRqqxsbylbDao.find(hql);
    }
}
